if exists (select 1 from sysobjects where name = 'set_neuplan' and type = 'P')
begin
   drop procedure set_neuplan
   print 'Procedure: set_neuplan deleted ...'
end
go
create procedure set_neuplan(
  @planname          varchar(60)   = 'Januar',
  @atid              int           = 1,
  @schemaid          int           = 1
)
as
begin
  set nocount on
  declare    @datumvon    datetime
  declare    @datumbis    datetime
  declare    @jahrid      int
  declare    @mandid      char(2)
  
  select @datumvon = Von from ArbeitsTage where ATID = @atid
  select @datumbis = Bis from ArbeitsTage where ATID = @atid
  select @jahrid = JahrID from ArbeitsTage where ATID = @atid
  select @mandid = mandid from ArbeitsTage where ATID = @atid
  
   -- Status 1: Aktiv   2: Inaktiv
   insert into ArbeitsPlan (ATID, Name, JahrID, SchemaID, Von, Bis, Status, mandid) 
        values (@atid, @planname, @jahrid, @schemaid, @datumvon, @datumbis, 1, @mandid)

   -- Returns the planid of the newly created plan
   select planid = ArbeitsPlan.PlanID
     from ArbeitsPlan
    where ArbeitsPlan.Von = @datumvon
      and ArbeitsPlan.Bis = @datumbis
      and ArbeitsPlan.Name = @planname
      and ArbeitsPlan.Status = 1

end
go
print 'Procedure: set_neuplan done ...'
go
grant exec on set_neuplan to prsadmins with grant option
go
grant exec on set_neuplan to prsusers
go

